var express = require('express');
//cnpm install express-connection --save
var mysql = require('mysql'); //引用中间件

var router = express.Router();
var app = express();

var connection = mysql.createConnection({     
  host     : 'localhost',       
  user     : 'root',              
  password : '123456',       
  port: '3306',                   
  database: 'test',
  charset : 'UTF8MB4_UNICODE_CI'  
}); 
 
connection.connect();
handleDisconnect(connection);
//写入cookie
router.get('/add', function (req, res) {

    var  addSql = 'INSERT INTO websites(Id,name,url,alexa,country) VALUES(0,?,?,?,?)';
    var  addSqlParams = ['baidu', 'https://www.baidu.com','23453', 'CN'];
    
    connection.query(addSql,addSqlParams,function (err, result) {
        if(err){
         console.log('[INSERT ERROR] - ',err.message);
         return;
        }        
 
       console.log('--------------------------INSERT----------------------------');
       //console.log('INSERT ID:',result.insertId);        
       console.log('INSERT ID:',result);        
       console.log('-----------------------------------------------------------------\n\n');  
       res.send(result);
    });
    //connection.end();

});

router.get('/select', function (req, res) {
   
    var  sql = 'SELECT * FROM websites';
     //查
    connection.query(sql,function (err, result) {
            if(err){
              console.log('[SELECT ERROR] - ',err.message);
              return;
            }
     
           console.log('--------------------------SELECT----------------------------');
           console.log(result);
           console.log('------------------------------------------------------------\n\n');  
           res.send(result);
    });
    //connection.end();

});

router.get('/update', function (req, res) {
    connection.connect();
    var modSql = 'UPDATE websites SET name = ?,url = ? WHERE name = ?';
    var modSqlParams = ['baidu', 'https://m.runoob.com','baidu'];
    //改
    connection.query(modSql,modSqlParams,function (err, result) {
       if(err){
             console.log('[UPDATE ERROR] - ',err.message);
             return;
       }        
      console.log('--------------------------UPDATE----------------------------');
      console.log('UPDATE affectedRows',result.affectedRows);
      console.log('-----------------------------------------------------------------\n\n');
      res.send(result);
    });
    
    //connection.end();

});

router.get('/delete', function (req, res) {
    var delSql = 'DELETE FROM websites where name="baidu"';
    //删
    connection.query(delSql,function (err, result) {
            if(err){
              console.log('[DELETE ERROR] - ',err.message);
              return;
            }        
     
           console.log('--------------------------DELETE----------------------------');
           console.log('DELETE affectedRows',result.affectedRows);
           console.log('-----------------------------------------------------------------\n\n'); 
           res.send(result);           
    });
    
    //connection.end();

});

function handleDisconnect(connection) {
    //监听错误事件
    connection.on('error', function(err) {
      if (!err.fatal) {
        return;
      }
   
      if (err.code !== 'PROTOCOL_CONNECTION_LOST') {
        throw err;
      }
   
      console.log('Re-connecting lost connection: ' + err.stack);
   
      connection = mysql.createConnection(connection.config);
      handleDisconnect(connection);
      connection.connect();
    });
}

module.exports = router;
